<?php
// +----------------------------------------------------------------------
// | ThinkCMF [ WE CAN DO IT MORE SIMPLE ]
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2017 http://www.thinkcmf.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: pl125 <xskjs888@163.com>
// +----------------------------------------------------------------------

namespace api\demo\controller;

use cmf\controller\RestBaseController;
use OpenApi\Annotations\Examples;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use think\facade\Db;

/**
 * Class IndexController
 * @package api\demo\controller
 */
class IndexController extends RestBaseController
{
    public function homePage(){
//        $id = input('id');
        $id=6582;
        if (empty($id)) {
            $this->error('数据不全');
        }
        $info = Db::name('plate')->where('id', $id)->find();
        if (!$info) {
            $this->error('数据不全');
        }
        $info['weight'] = $info['weight'] * 1000;
        $info['update_time'] = date('Y-m-d', $info["update_time"]);

//类型
        $type = array(1 => '成品库', 2 => '实验库', 3 => '废料库', 4 => '半成品库', 5 => '头尾库', 6 => '封闭库', 7 => '原料库', 8 => '委外库');

        $companytypea_arr = array(1 => '自主', 2 => '来料', 3 => '委外');

        $company_id_arr = Db::name('company')
            ->column('name', 'id');

        $ht_id_arr = Db::name('ht')
            ->column('ht_number', 'id');

        $grade_id_arr = Db::name('grade')
            ->column('g_name', 'id');

        $steel_grade_id_arr = Db::name('steel_grade')
            ->column('name', 'id');

        $plate_id_arr = Db::name('plate')
            ->column('name', 'id');
        $coil_id_arr_da = Db::name('coil')
            ->column('coilno', 'id');



        $info['grade_id'] = isset($grade_id_arr[$info['grade_id']]) ? $grade_id_arr[$info['grade_id']] : '';
        $info['steel_id'] = isset($steel_grade_id_arr[$info['steel_id']]) ? $steel_grade_id_arr[$info['steel_id']] : '';
        $info['coil_id'] = isset($coil_id_arr_da[$info['coil_id']]) ? $coil_id_arr_da[$info['coil_id']] : '';
        $info['ht_id'] = isset($ht_id_arr[$info['ht_id']]) ? $ht_id_arr[$info['ht_id']] : '';
        $info['company_id'] = isset($company_id_arr[$info['company_id']]) ? $company_id_arr[$info['company_id']] : '华星科技';
        $info['parent_id'] = isset($plate_id_arr[$info['parent_id']]) ? $plate_id_arr[$info['parent_id']] : '';
        $info['admin_id'] = isset($user_arr[$info['admin_id']]) ? $user_arr[$info['admin_id']] : '';
        $info['companytype'] = isset($companytypea_arr[$info['companytype']]) ? $companytypea_arr[$info['companytype']] : '';

        if ($info['type'] != 1&&$info['type']!=6){
            $info['type'] = isset($type[$info['type']]) ? $type[$info['type']] : '';
        }else{
            $info['type'] = isset($type[$info['type']])  ? $type[$info['type']].$info['grade_id'] : '';
        }
        $info['d'] = floatval($info['d']);
        $this->success('请求成功!', $info);
//        $this->success();
    }
    public function task()
    {
        $coil_id_arr_da = Db::name('plate')
            ->where('coil_id', 0)
            ->where('parent_id', '>', 0)
            ->field('id,coil_id,parent_id')
            ->page(1, 1)
            ->select();
        foreach ($coil_id_arr_da as $k => $v) {


            if ($v['parent_id'] > 0) {
//                var_dump($v);

                $data = $this->plateids($v);
//                var_dump($data);
            }
//            exit();
        }
//        var_dump($coil_id_arr_da);
    }

    public function plateids(&$data = [], $i = 0)
    {
        static $info = array();
//        var_dump($data);
        if (count($data)>0) {
            if ($data['parent_id'] == 0) {
                $info = Db::name('plate')
                    ->where('id', $data['parent_id'])
                    ->find();
//            var_dump($info);
//        var_dump($info['coil_id']);
                if (isset($info) && $info['coil_id'] > 0) {
                    var_dump($info);
//                    return $info;
                }
            }
        }

        $this->plateids($info);
    }


    public function index()
    {
//        $objPHPExcel = new \PHPExcel();
        $file = 'upload/admin/20221121/91a498f5378961a94384ce91afbef80f.xlsx';
        $data = $this->importExecl($file);
        $newdata = $data;
        $count = count($data[1]) - 1;
        $strTable = '<table width="500" border="1">';
        $strTable .= '<tr>';
        $strTable .= '<td style="text-align:center;" colspan="' . $count . '" rowspan="2">华星机械大车班排班表</td>';

        $strTable .= '</tr>';
        $strTable .= '<tr>';
        $strTable .= '</tr>';
        $strTable .= '<tr>';
        $strTable .= '<td colspan="' . $count . '" rowspan="2">注意：必须严格按照现有格式，否则无法识别到考勤后台</td>';
        $strTable .= '</tr>';
        $strTable .= '<tr>';
        $strTable .= '</tr>';
        $strTable .= '<tr>';
        $strTable .= '<td colspan="' . $count . '"  rowspan="2">' . $data[2][0] . '</td>';

        $strTable .= '</tr>';
        $strTable .= '<tr>';
        $strTable .= '</tr>';
        $strTable .= '<tr>';
        $title = $data[1][0];
        $mon = substr($title, 0, 6);
        $mon = substr_replace($mon, '-', 4, 0);
        $strTable .= '<td style="text-align:center;">部门</td>';
        $strTable .= '<td style="text-align:center;">编号</td>';
        $strTable .= '<td style="text-align:center;">姓名</td>';

        foreach ($data[4] as $k1 => $v1) {
            if ($k1 > 3) {
                if ($v1 < 10) {
                    $strTable .= '<td style="text-align:center;">' . date("Y-m-d", strtotime($mon . '-0' . $v1)) . ' ' . '</td>';
                } else {
                    $strTable .= '<td style="text-align:center;">' . date("Y-m-d", strtotime($mon . '-' . $v1)) . ' ' . '</td>';
                }

            }

        }
        $strTable .= '</tr>';
        foreach ($data as $k => $v) {

            if ($k > 4) {
                $strTable .= '<tr>';
//                var_dump($v);
//                var_dump(count($v));
                $d = explode('/', $v[2]);
                $strTable .= '<td style="text-align:center;">' . $d[count($d) - 1] . '</td>';
                $strTable .= '<td style="text-align:center;">' . $v[1] . '</td>';
                $strTable .= '<td style="text-align:center;">' . $v[0] . '</td>';
                foreach ($v as $key => $val) {
//                    var_dump($val);
                    if ($key > 3) {
                        $strTable .= '<td style="text-align:center;">' . $val . '</td>';
                    }
                }
                $strTable .= '</tr>';
//                exit(/);
            }

        }
        $strTable .= '</table>';
//        var_dump($strTable);
        downloadExcel($strTable, 'users_');
        exit();
//        var_dump($data);

//        var_dump($data[1][0]);
        exit();


//        excel_out('钢卷导出' . date('ymdhis', time()), $newdata, $head, $keys);
    }

    /**
     * 使用PHPEXECL导入
     *
     * @param string $file 文件地址
     * @param int $sheet 工作表sheet(传0则获取第一个sheet)
     * @param int $columnCnt 列数(传0则自动获取最大列)
     * @param array $options 操作选项
     *                          array mergeCells 合并单元格数组
     *                          array formula    公式数组
     *                          array format     单元格格式数组
     *
     * @return array
     * @throws Exception
     */
    function importExecl(string $file = '', int $sheet = 0, int $columnCnt = 0, &$options = [])
    {
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) or !file_exists($file)) {
                throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (!$objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (!$objRead->canRead($file)) {
                    throw new \Exception('只支持导入Excel文件！');
                }
            }

            /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel对象 */
            $obj = $objRead->load($file);
            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            if (0 == $columnCnt) {
                /* 取得最大的列号 */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原逻辑，循环时使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data = [];

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId = $cellName . $_row;
                    $cell = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式，公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }
//                    $excelData = array();

//var_dump( trim($currSheet->getCell($cellId)->getFormattedValue()));
                    $data[$_row][] = trim($currSheet->getCell($cellId)->getFormattedValue());
//                    var_dump(  $data[$_row]);
//                    return $excelData;

//                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

//                    if (!empty($data[$_row])) {
//                        $isNull = false;
//                    }
                }

//                /* 判断是否整行数据为空，是的话删除该行数据 */
//                if ($isNull) {
//                    unset($data[$_row]);
//                }
            }

            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }
}
